#!/usr/bin/perl

package Ddl;

use strict;
use warnings;
use lib "../libs";

use Exporter qw(import);
use JSON;
use File::Find;
use File::Basename;
use Const ();
use App ();
use Utils ();
use Utils qw(dd);
use Data::Dumper qw(Dumper);
use Log;
use Time::Piece;
use Encode qw(encode decode);
use utf8;
use open qw(:std :utf8);
binmode STDOUT, ":utf8";
use Carp qw(croak);



sub export {

    # 使用 Const 模块中定义的输入文件路径
    my ($input_file_name) = @_;
    my $input_file = $Const::INPUT_PATH . $Const::DS . "$input_file_name" ;
    # dd($input_file);

    # 打开输入文件
    open my $fh, '<', $input_file or die "Cannot open file $input_file: $!";

    # 定义一个数组来存储表名
    my @tables;

    while (my $line = <$fh>) {
        chomp $line;  # 去掉行尾的换行符

        # 跳过空行和注释行（假设注释行以 "#" 开头）
        next if $line =~ /^\s*$/;  # 空行
        next if $line =~ /^\s*#/;  # 注释行

        # 假设每一行的内容就是一个表名
        my $table_name = $line;

        # 将表名存储到数组中
        push @tables, $table_name;
    }
    # 关闭文件句柄
    close $fh;

    # 打印所有表名
    # dd(@tables);
    print "All tables: " . join(", ", @tables) . "\n";

    # 加载数据库配置
    my $db_config_file = $Const::DB_CONFIG_PATH. $Const::DS . "guass_config.json";
    open my $config_fh, '<', $db_config_file or die "Cannot open file $db_config_file: $!";
    my $db_config_json = do { local $/; <$config_fh> };
    close $config_fh;

    my $db_config = decode_json($db_config_json);
    my $db = App::getDB($db_config);

    # 确保输出目录存在
    use File::Path qw(make_path);
    make_path($Const::OUTPUT_PATH) unless -d $Const::OUTPUT_PATH;

    # 遍历表名数组，查询每个表的 DDL
    foreach my $table_name (@tables) {
        my $result = get_pg_tabledef($db, $table_name);
        # dd($result);
        my $file_name = $Const::OUTPUT_PATH . $Const::DS . "${table_name}.sql";

        # 打开文件句柄，准备写入
        open my $out_fh, '>', $file_name or die "Cannot open file $file_name: $!";

        if (defined $result) {
            # 如果查询成功，将结果写入文件
            print $out_fh $result;
        } else {
            # 如果查询失败，将错误信息写入文件
            print $out_fh "Error: Failed to fetch data for table $table_name\n";
        }
        # 关闭文件句柄
        close $out_fh;
        Log::info("Output_path: $file_name");
    }
    

    # 关闭数据库连接
    App::closeDB($db_config);
}

sub get_pg_tabledef {
    my ($db, $table_name) = @_;

    unless (defined($db)) {
        croak "Invalid db";
    }

    unless ($db->{config}->{type} eq "PostgreSQL") {
        croak "Unsupported database type: $db->{config}->{type}";
    }

    # 构造 SQL 查询
    my $sql = "SELECT pg_get_tabledef('$table_name')";
    Log::info("  Execute SQL: $sql");

    # 使用 eval 块捕获异常
    my $result;
    eval {
        # 准备并执行 SQL 查询
        my $sth = $db->{dbh}->prepare($sql);
        $sth->execute();

        # 获取查询结果
        $result = $sth->fetchrow_array();
        # dd($result);
        $sth->finish();
    };

    if ($@) {
        # 如果捕获到异常，记录错误信息
        Log::error("Failed to fetch data for table $table_name: $@");

        # 检查是否是因为表不存在
        if ($@ =~ /relation "$table_name" does not exist/) {
            Log::error("Table $table_name does not exist.");
        }

        # 确保事务被正确处理
        if ($db->{dbh}->{AutoCommit} == 0) {
            $db->{dbh}->rollback();
        }

        return undef;
    }

    return $result;
}

sub check {
    my $output_path = $Const::OUTPUT_PATH;  # 获取输出路径
    my $checked_output_path = "$output_path/checked";  # 创建一个新的输出目录

    # 确保新的输出目录存在
    make_path($checked_output_path) unless -d $checked_output_path;

    # 检查输出路径是否存在
    unless (-d $output_path) {
        die "Output path does not exist: $output_path";
    }

    # 遍历输出路径下的所有 .sql 文件
    opendir(my $dir, $output_path) or die "Cannot open directory $output_path: $!";
    my @sql_files = grep { /\.sql$/ } readdir($dir);
    closedir($dir);

    foreach my $file_name (@sql_files) {
        my $file_path = "$output_path/$file_name";

        # 读取 SQL 文件内容
        open my $fh, '<', $file_path or die "Cannot open file $file_path: $!";
        my $content = do { local $/; <$fh> };
        close $fh;

        # 检查文件内容是否为空
        unless (defined $content && $content ne '') {
            Log::error("File content is empty or undefined: $file_path");
            next;
        }
        Log::info("--- checking $file_name ---");

        if ($content =~ /CREATE\s+TABLE\s+(\w+)\s+.*?orientation=(\w+)/is) {
            my $table_name = $1;  # 表名
            my $storage_type = $2;  # 当前的存储类型
            # dd($table_name);
            # dd($storage_type);

            # 检查存储类型
            my $expected_storage_type = check_storage_type($table_name);
            if ($expected_storage_type && $expected_storage_type ne $storage_type) {
                Log::info("$table_name 存储类型不符合预期，修正为 $expected_storage_type");
                $content =~ s/orientation=$storage_type/orientation=$expected_storage_type/;
            } else {
                Log::info("$table_name 存储类型符合预期，不需要修正");
            }
            # 检查分布类型
            if ($table_name =~ /^cd_/i) {
                if ($content =~ /DISTRIBUTE\s+BY\s+(\w+)/i) {
                    my $current_distribution_type = $1;  # 当前的分布类型
                    my $expected_distribution_type = check_distribution_type($table_name);
                    if ($expected_distribution_type && $expected_distribution_type ne $current_distribution_type) {
                        Log::info("$table_name 分布类型不符合预期，修正为 $expected_distribution_type");
                        $content =~ s/DISTRIBUTE\s+BY\s+\w+/DISTRIBUTE BY $expected_distribution_type/;
                    } else {
                        Log::info("$table_name 分布类型符合预期，不需要修正");
                    }
                } else {
                    Log::error("$table_name 没有找到 DISTRIBUTE BY 子句");
                }
            }
            

            #### 测试，在表名后拼一个”_5“
            my $table_name_test = temp_rename_tables($table_name);
            $content =~ s/$table_name/$table_name_test/;

            # 写入修正后的内容到新文件
            my $new_file_path = "$checked_output_path/$file_name";
            open my $out_fh, '>', $new_file_path or die "Cannot open file $new_file_path: $!";
            print $out_fh $content;
            close $out_fh;

            Log::info("Checked file: $new_file_path");
        } else {
            Log::error("Failed to parse SQL file: $file_path");
        }
    }
}

sub check_storage_type {
    my ($table_name) = @_;

    if ($table_name =~ /^cd_/i) {
        return 'row';
    } else {
        return 'column';
    }
}

sub check_distribution_type {
    my ($table_name) = @_;

    if ($table_name =~ /^cd_/i) {
        return 'REPLICATION';
    } else {
        return 'ROUNDROBIN';
    }
}

sub temp_rename_tables {
    my ($table_name) = @_;
    return $table_name . "_5"; 
}

sub create {
    my ($directory) = @_;
    dd($directory);

    # 检查目录是否存在
    unless (-d $directory) {
        die "Directory does not exist: $directory";
    }

    # 读取下游配置文件
    my $config_file = "$Const::DB_CONFIG_PATH/guass_config_dst.json";
    open my $config_fh, '<', $config_file or die "Cannot open file $config_file: $!";
    my $config_json = do { local $/; <$config_fh> };
    close $config_fh;

    my $config = decode_json($config_json);

    # 初始化数据库连接
    App::initDBConfig($config);
    my $db = App::getDB($config);

    # 遍历目录中的所有 .sql 文件
    opendir(my $dir, $directory) or die "Cannot open directory $directory: $!";
    my @sql_files = grep { /\.sql$/ } readdir($dir);
    closedir($dir);

    foreach my $file_name (@sql_files) {
        my $file_path = "$directory/$file_name";

        # 读取 SQL 文件内容
        open my $fh, '<', $file_path or die "Cannot open file $file_path: $!";
        my $content = do { local $/; <$fh> };
        close $fh;

        # 执行 SQL 文件中的建表操作
        eval {
            my $result = App::createTable($db, $content);
            if ($result) {
                Log::info("Table created successfully: $file_name");
            } else {
                Log::error("Failed to create table: $file_name");
            }
        };
        if ($@) {
            Log::error("Error creating table $file_name: $@");
        }
    }

    # 关闭数据库连接
    App::closeDB($config);
}
